﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using BusinessLibrary.BLL;


namespace BusinessLibrary.DAL
{
    class SqlAuthentification
    {
        private static Authentification ReaderToObject(SqlDataReader dr)
        {
            Authentification _Authentification = new Authentification();
            _Authentification.Id = int.Parse(dr["Id"].ToString());

            _Authentification.Login = dr["Login"].ToString();
            _Authentification.Pass = dr["Pass"].ToString();
            _Authentification.Droit = dr["Droit"].ToString();
           // _Authentification.Actif= dr["Actif"].ToString();
            if (!object.ReferenceEquals(dr["Actif"], DBNull.Value))//a voir fel insertion
            {
                _Authentification.Actif = dr["Actif"].ToString();
            }

            return _Authentification;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Authentification GetAuthentificationById(int id)
        {
            Authentification authentification = new Authentification();
            string strSQL = @"select * from Authentification where Id=@Id";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        authentification = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return authentification;
        }

        public static int InsertAuthentification(Authentification A)
        {
            int n = 0;
            string strSQL = @"insert into Authentification (Login,Pass,Droit,Actif) values(@Login,@Pass,@Droit,@Actif)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Login", A.Login);
                    cmd.Parameters.AddWithValue("@Pass", A.Pass );
                    cmd.Parameters.AddWithValue("@Droit", A.Droit );
                    cmd.Parameters.AddWithValue("@Actif", A.Actif );
                 
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;

        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateScolarite(Scolarite s)
        {
            int n = 0;

            string strSQl = @"UPDATE Scolarite SET (@nom IS NULL OR Nom=@nom) ,(@cin is null or Cin=@cin), (@prenom is null or Prenom=@prenom),"
           + " (@num_tel  is null or Num_tel=@num_tel), Email=@email, Id_authentification=@id_authentification where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();

                    SqlCommand cmd = new SqlCommand(strSQl, cnx);
                    cmd.Parameters.AddWithValue("@cin", s.Cin);
                    cmd.Parameters.AddWithValue("@nom", s.Nom);
                    cmd.Parameters.AddWithValue("@prenom", s.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", s.Num_tel);
                    cmd.Parameters.AddWithValue("@email", s.Email);
                    cmd.Parameters.AddWithValue("@id_authentification", s.authentificationScolarit.Id);
                    n = cmd.ExecuteNonQuery();

                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }

         
    }
}
